Implementing Database Entities, DAOs, and Relationships in Android Room

Course: Android App Development

Topic: Implementing Database Entities, DAOs, and Relationships

Course Link: Android App Development Training

Demo Link: Register for a Course Demo

Website: JustAcademy

1. Introduction

Android applications frequently need to store structured information such as users, products, orders, courses, tasks, and categories. When this information contains multiple related records, a relational database is an effective way to organize and retrieve it.

Room is an Android Jetpack persistence library that provides an abstraction layer over SQLite. It helps developers define database tables, execute queries, manage records, and model relationships using Kotlin classes and annotations.

Three important components form the foundation of a Room database:

• Entities: Define database tables and their columns.
• Data Access Objects (DAOs): Define the operations used to insert, retrieve, update, and delete records.
• Relationships: Connect records across tables using foreign keys, junction tables, and relationship query models.

For example, in an online learning application, one instructor may teach multiple courses, and each course may contain multiple lessons. Properly designed entities and relationships allow the app to represent this structure without unnecessarily duplicating data.

2. Learning Objectives

• Understand the purpose of Room entities and DAOs.
• Create entities with primary keys, custom column names, indexes, and constraints.
• Implement CRUD operations using DAO annotations.
• Understand foreign keys and referential integrity.
• Implement one-to-one, one-to-many, and many-to-many relationships.
• Use @Embedded, @Relation, and @Junction.
• Retrieve related records using transactions and SQL joins.
• Use Kotlin Flow and coroutines for asynchronous database access.
• Organize Room code using repositories and ViewModels.
• Apply migration, performance, and testing best practices.

3. Understanding Database Entities

An entity is a Kotlin class annotated with @Entity. Room uses it to define a database table. Each entity instance represents one row, and each persisted property represents a column.

Consider a course application containing a table named students. Each student has a unique ID, a name, an email address, and an enrollment date.

3.1 Creating a Student Entity

import androidx.room.ColumnInfo
import androidx.room.Entity
import androidx.room.Index
import androidx.room.PrimaryKey

@Entity(
    tableName = "students",
    indices = [
        Index(value = ["email"], unique = true)
    ]
)
data class StudentEntity(
    @PrimaryKey(autoGenerate = true)
    val studentId: Long = 0,

    @ColumnInfo(name = "full_name")
    val fullName: String,

    val email: String,

    @ColumnInfo(name = "enrolled_at")
    val enrolledAt: Long = System.currentTimeMillis()
)

3.2 Understanding the Annotations

Room normally derives the table name from the class name and column names from the properties. Explicit names are useful when the database schema should remain stable even if Kotlin property names change.

4. Primary Keys and Composite Keys

A primary key uniquely identifies each row in a table. It may be an automatically generated numeric ID, a stable identifier supplied by the application, or a combination of columns.

4.1 Single Primary Key

@Entity(tableName = "categories")
data class CategoryEntity(
    @PrimaryKey(autoGenerate = true)
    val categoryId: Long = 0,
    val name: String
)

4.2 Composite Primary Key

A composite primary key uses multiple columns together to identify a row. It is useful when the combination, rather than either value independently, must be unique.

@Entity(
    tableName = "student_course_enrollments",
    primaryKeys = ["studentId", "courseId"]
)
data class StudentCourseEnrollmentEntity(
    val studentId: Long,
    val courseId: Long,
    val enrolledAt: Long = System.currentTimeMillis()
)

In this example, a student cannot have duplicate enrollment rows for the same course if the composite key is respected.

5. Creating Data Access Objects (DAOs)

A DAO defines how application code interacts with database records. Instead of writing database connection management and cursor-handling code manually, developers declare DAO functions and allow Room to generate their implementations.

A DAO can use convenience annotations such as @Insert, @Update, and @Delete, or custom SQL queries through @Query.

5.1 Creating StudentDao

import androidx.room.Dao
import androidx.room.Delete
import androidx.room.Insert
import androidx.room.OnConflictStrategy
import androidx.room.Query
import androidx.room.Update
import kotlinx.coroutines.flow.Flow

@Dao
interface StudentDao {

    @Insert(onConflict = OnConflictStrategy.ABORT)
    suspend fun insertStudent(student: StudentEntity): Long

    @Insert(onConflict = OnConflictStrategy.ABORT)
    suspend fun insertStudents(students: List<StudentEntity>)

    @Update
    suspend fun updateStudent(student: StudentEntity)

    @Delete
    suspend fun deleteStudent(student: StudentEntity)

    @Query("SELECT * FROM students ORDER BY full_name ASC")
    fun observeAllStudents(): Flow<List<StudentEntity>>

    @Query("SELECT * FROM students WHERE studentId = :id LIMIT 1")
    suspend fun getStudentById(id: Long): StudentEntity?

    @Query("SELECT * FROM students WHERE email = :email LIMIT 1")
    suspend fun findStudentByEmail(email: String): StudentEntity?

    @Query("DELETE FROM students WHERE studentId = :id")
    suspend fun deleteStudentById(id: Long): Int
}

5.2 DAO Annotations Explained

Important: Use suspend for one-time database operations so they can run through coroutines. Use Flow for queries that should emit updated results as observed database tables change.

6. Understanding Foreign Keys

A foreign key connects a column in one table to a primary key or eligible unique key in another table. It helps maintain referential integrity by preventing invalid references and can define what happens when a referenced row is deleted or updated.

Suppose every course belongs to an instructor. The course table stores the instructor's ID rather than repeating the instructor's name and email for every course.

@Entity(tableName = "instructors")
data class InstructorEntity(
    @PrimaryKey(autoGenerate = true)
    val instructorId: Long = 0,
    val name: String,
    val email: String
)

@Entity(
    tableName = "courses",
    foreignKeys = [
        androidx.room.ForeignKey(
            entity = InstructorEntity::class,
            parentColumns = ["instructorId"],
            childColumns = ["instructorOwnerId"],
            onDelete = androidx.room.ForeignKey.RESTRICT
        )
    ],
    indices = [androidx.room.Index("instructorOwnerId")]
)
data class CourseEntity(
    @PrimaryKey(autoGenerate = true)
    val courseId: Long = 0,
    val title: String,
    val instructorOwnerId: Long
)

The instructorOwnerId column references InstructorEntity.instructorId. The index helps queries that filter courses by instructor.

6.1 Foreign Key Delete Rules

Choose a delete rule based on the application's requirements. For example, cascading deletion is not always suitable when records must be retained for auditing or reporting.

7. Types of Relationships in Room

Room supports several common ways to represent relationships between records.

Room does not automatically load related entities by following object references inside entity classes. Instead, define the relational schema and query the required related data explicitly. You can use relationship data classes with @Relation or SQL queries with suitable return types.

8. Implementing a One-to-One Relationship

In a one-to-one relationship, one record in a table is associated with at most one record in another table. For example, a user may have one profile.

8.1 Define the Entities

@Entity(tableName = "app_users")
data class AppUserEntity(
    @PrimaryKey(autoGenerate = true)
    val userId: Long = 0,
    val username: String
)

@Entity(
    tableName = "user_profiles",
    foreignKeys = [
        androidx.room.ForeignKey(
            entity = AppUserEntity::class,
            parentColumns = ["userId"],
            childColumns = ["ownerUserId"],
            onDelete = androidx.room.ForeignKey.CASCADE
        )
    ],
    indices = [
        androidx.room.Index(value = ["ownerUserId"], unique = true)
    ]
)
data class UserProfileEntity(
    @PrimaryKey(autoGenerate = true)
    val profileId: Long = 0,
    val ownerUserId: Long,
    val biography: String
)

The unique index on ownerUserId prevents multiple profile rows from referring to the same user. Without that uniqueness constraint, the database would allow multiple profiles for one user, which would be a one-to-many structure.

8.2 Create a Relationship Data Class

import androidx.room.Embedded
import androidx.room.Relation

data class UserWithProfile(
    @Embedded
    val user: AppUserEntity,

    @Relation(
        parentColumn = "userId",
        entityColumn = "ownerUserId"
    )
    val profile: UserProfileEntity?
)

The @Embedded annotation includes the user columns in the result object. The @Relation annotation tells Room how to find the matching profile using the parent's primary key and the profile's foreign key.

8.3 Query the Relationship

import androidx.room.Dao
import androidx.room.Query
import androidx.room.Transaction

@Dao
interface UserProfileDao {

    @Transaction
    @Query("SELECT * FROM app_users WHERE userId = :userId")
    suspend fun getUserWithProfile(userId: Long): UserWithProfile?
}

@Transaction ensures that the related-data query is executed within a transaction, providing a consistent result while Room loads the relationship.

9. Implementing a One-to-Many Relationship

In a one-to-many relationship, a single parent can have multiple child records. For example, an instructor may teach multiple courses, but each course in this model has one instructor.

9.1 Define the Entities

@Entity(tableName = "teachers")
data class TeacherEntity(
    @PrimaryKey(autoGenerate = true)
    val teacherId: Long = 0,
    val teacherName: String
)

@Entity(
    tableName = "teacher_courses",
    foreignKeys = [
        androidx.room.ForeignKey(
            entity = TeacherEntity::class,
            parentColumns = ["teacherId"],
            childColumns = ["teacherId"],
            onDelete = androidx.room.ForeignKey.CASCADE
        )
    ],
    indices = [androidx.room.Index("teacherId")]
)
data class TeacherCourseEntity(
    @PrimaryKey(autoGenerate = true)
    val courseId: Long = 0,
    val teacherId: Long,
    val courseTitle: String
)

9.2 Create the Relationship Data Class

import androidx.room.Embedded
import androidx.room.Relation

data class TeacherWithCourses(
    @Embedded
    val teacher: TeacherEntity,

    @Relation(
        parentColumn = "teacherId",
        entityColumn = "teacherId"
    )
    val courses: List<TeacherCourseEntity>
)

9.3 Add a DAO Query

@Dao
interface TeacherDao {

    @Transaction
    @Query("SELECT * FROM teachers WHERE teacherId = :id")
    suspend fun getTeacherWithCourses(id: Long): TeacherWithCourses?

    @Transaction
    @Query("SELECT * FROM teachers ORDER BY teacherName")
    fun observeTeachersWithCourses(): Flow<List<TeacherWithCourses>>
}

When the query returns a teacher, Room also loads the associated course records. A teacher with no courses has an empty list in the relationship object.

10. Implementing a Many-to-Many Relationship

A many-to-many relationship occurs when multiple records in one table can relate to multiple records in another. In a learning platform, a student can enroll in many courses, and each course can contain many students.

Relational databases represent this structure using a junction table. The junction table stores one row for each student-course association.

10.1 Create the Student and Course Entities

@Entity(tableName = "students")
data class StudentEntity(
    @PrimaryKey(autoGenerate = true)
    val studentId: Long = 0,
    val fullName: String
)

@Entity(tableName = "courses")
data class CourseEntity(
    @PrimaryKey(autoGenerate = true)
    val courseId: Long = 0,
    val title: String
)

10.2 Create the Junction Entity

@Entity(
    tableName = "student_course_cross_ref",
    primaryKeys = ["studentId", "courseId"],
    foreignKeys = [
        androidx.room.ForeignKey(
            entity = StudentEntity::class,
            parentColumns = ["studentId"],
            childColumns = ["studentId"],
            onDelete = androidx.room.ForeignKey.CASCADE
        ),
        androidx.room.ForeignKey(
            entity = CourseEntity::class,
            parentColumns = ["courseId"],
            childColumns = ["courseId"],
            onDelete = androidx.room.ForeignKey.CASCADE
        )
    ],
    indices = [
        androidx.room.Index("courseId")
    ]
)
data class StudentCourseCrossRef(
    val studentId: Long,
    val courseId: Long
)

The composite primary key prevents the same student-course association from being inserted twice. The foreign keys prevent the junction table from referencing nonexistent students or courses.

10.3 Define the Relationship Data Classes

import androidx.room.Embedded
import androidx.room.Junction
import androidx.room.Relation

data class StudentWithCourses(
    @Embedded
    val student: StudentEntity,

    @Relation(
        parentColumn = "studentId",
        entityColumn = "courseId",
        associateBy = Junction(
            value = StudentCourseCrossRef::class,
            parentColumn = "studentId",
            entityColumn = "courseId"
        )
    )
    val courses: List<CourseEntity>
)

data class CourseWithStudents(
    @Embedded
    val course: CourseEntity,

    @Relation(
        parentColumn = "courseId",
        entityColumn = "studentId",
        associateBy = Junction(
            value = StudentCourseCrossRef::class,
            parentColumn = "courseId",
            entityColumn = "studentId"
        )
    )
    val students: List<StudentEntity>
)

10.4 Create DAO Queries

@Dao
interface EnrollmentDao {

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    suspend fun enrollStudent(
        enrollment: StudentCourseCrossRef
    ): Long

    @Transaction
    @Query("SELECT * FROM students WHERE studentId = :studentId")
    suspend fun getStudentWithCourses(
        studentId: Long
    ): StudentWithCourses?

    @Transaction
    @Query("SELECT * FROM courses WHERE courseId = :courseId")
    suspend fun getCourseWithStudents(
        courseId: Long
    ): CourseWithStudents?
}

The junction table makes the many-to-many relationship explicit and supports queries in both directions. If enrollment requires additional information such as enrollment date, payment status, or grade, the junction entity can store those fields too.

11. Understanding @Embedded

The @Embedded annotation allows Room to flatten the properties of a nested object into columns of the containing entity. It is appropriate when the nested data belongs to the containing record and does not need an independent table relationship.

import androidx.room.ColumnInfo
import androidx.room.Embedded
import androidx.room.Entity
import androidx.room.PrimaryKey

data class Address(
    val street: String,
    val city: String,
    @ColumnInfo(name = "postal_code")
    val postalCode: String
)

@Entity(tableName = "customer_profiles")
data class CustomerProfileEntity(
    @PrimaryKey
    val customerId: Long,
    val customerName: String,

    @Embedded(prefix = "address_")
    val address: Address
)

The address fields become columns in the same table, such as address_street, address_city, and address_postal_code. A prefix helps avoid column-name collisions when several embedded objects contain similarly named properties.

12. SQL JOIN Queries for Related Data

Relationship data classes are not the only way to retrieve related records. SQL joins can combine data from multiple tables into a result set. This is especially useful when the UI needs selected columns rather than complete entity objects.

data class StudentCourseSummary(
    val studentName: String,
    val courseTitle: String
)

@Dao
interface CourseSummaryDao {

    @Query(
        """
        SELECT students.fullName AS studentName,
               courses.title AS courseTitle
        FROM students
        INNER JOIN student_course_cross_ref
            ON students.studentId = student_course_cross_ref.studentId
        INNER JOIN courses
            ON courses.courseId = student_course_cross_ref.courseId
        ORDER BY students.fullName, courses.title
        """
    )
    suspend fun getStudentCourseSummaries(): List<StudentCourseSummary>
}

The query joins the students table to the junction table and then to the courses table. The aliases in the SQL result must match the properties in StudentCourseSummary so Room can map the selected columns correctly.

13. Creating the Room Database Class

Every entity used by Room must be registered in the database's @Database annotation. The database class also exposes a DAO accessor for each DAO.

import androidx.room.Database
import androidx.room.RoomDatabase

@Database(
    entities = [
        StudentEntity::class,
        CourseEntity::class,
        StudentCourseCrossRef::class
    ],
    version = 1,
    exportSchema = true
)
abstract class AppDatabase : RoomDatabase() {
    abstract fun studentDao(): StudentDao
    abstract fun enrollmentDao(): EnrollmentDao
}

This example assumes that the listed classes and DAOs are declared in the same project with the corresponding imports. If you use the teacher or profile examples as well, add their entities to the database and expose their DAOs too.

14. Building a Database Instance

For a typical single-process application, use a shared database instance rather than creating a new instance every time a screen is opened.

import android.content.Context
import androidx.room.Room

object DatabaseProvider {

    @Volatile
    private var instance: AppDatabase? = null

    fun getDatabase(context: Context): AppDatabase {
        return instance ?: synchronized(this) {
            instance ?: Room.databaseBuilder(
                context.applicationContext,
                AppDatabase::class.java,
                "learning_database"
            ).build().also {
                instance = it
            }
        }
    }
}

Use the application context so the database does not retain an Activity. If the application uses multiple processes, review Room's multi-instance invalidation requirements and configuration.

15. Insert Related Records Correctly

When creating related records, the referenced parent must exist before a child can reference it. With auto-generated IDs, capture the ID returned by the insert operation and use it when creating the child record.

suspend fun createStudentAndEnroll(
    studentDao: StudentDao,
    courseId: Long,
    studentName: String
) {
    val studentId = studentDao.insertStudent(
        StudentEntity(fullName = studentName)
    )

    // Insert the junction row using the generated student ID.
    // The referenced course must already exist.
    // enrollmentDao.enrollStudent(
    //     StudentCourseCrossRef(
    //         studentId = studentId,
    //         courseId = courseId
    //     )
    // )
}

This snippet illustrates the sequence; it assumes a matching insertStudent() DAO method and a valid existing course. In a real application, related inserts should often be grouped in a transaction so that partial writes do not leave inconsistent data.

16. Use Transactions for Related Operations

Transactions are important when multiple database changes must succeed or fail together. For example, creating an enrollment might involve inserting a student, validating a course, and adding a junction-table row.

import androidx.room.Transaction

@Dao
abstract class EnrollmentManagementDao {

    @Insert
    abstract suspend fun insertStudent(
        student: StudentEntity
    ): Long

    @Insert
    abstract suspend fun insertEnrollment(
        enrollment: StudentCourseCrossRef
    )

    @Transaction
    open suspend fun registerStudentForCourse(
        student: StudentEntity,
        courseId: Long
    ) {
        val studentId = insertStudent(student)

        insertEnrollment(
            StudentCourseCrossRef(
                studentId = studentId,
                courseId = courseId
            )
        )
    }
}

For this example, the database must include all referenced entities, and the target course must already exist. If inserting the enrollment fails, the transaction rolls back the student insertion as well. Depending on the project structure, you can also implement the transaction in a repository using Room's supported transaction APIs.

17. Handling CRUD Operations with Relationships

Deleting a row from the junction table normally removes only the relationship, not the student or course itself. Foreign-key delete rules govern what happens when a parent record is deleted.

18. DAO Return Types and Reactive Data

DAO methods can return different types depending on whether the operation is a one-time action or an observable query.

Use Flow for database-driven screens that should react to changes. One-time suspend queries are suitable for operations such as checking whether a record exists before proceeding.

19. Using Entities and DAOs with a Repository

A repository separates persistence logic from the ViewModel and UI. It also provides a place for validation and business rules.

import kotlinx.coroutines.flow.Flow

class StudentRepository(
    private val studentDao: StudentDao
) {
    val students: Flow<List<StudentEntity>> =
        studentDao.observeAllStudents()

    suspend fun addStudent(
        name: String,
        email: String
    ): Long {
        require(name.isNotBlank()) {
            "Student name cannot be empty"
        }

        require(email.isNotBlank()) {
            "Email cannot be empty"
        }

        return studentDao.insertStudent(
            StudentEntity(
                fullName = name.trim(),
                email = email.trim()
            )
        )
    }

    suspend fun deleteStudent(student: StudentEntity) {
        studentDao.deleteStudent(student)
    }
}

The database's unique email index still protects against duplicate email values even if application-level validation passes. In a production app, handle constraint violations and report a useful message to the user.

20. Integrating Room with a ViewModel

The ViewModel exposes database data to the UI and launches writes in a coroutine scope. Configure a ViewModel factory or dependency-injection framework to supply the repository.

import androidx.lifecycle.ViewModel
import androidx.lifecycle.viewModelScope
import kotlinx.coroutines.flow.SharingStarted
import kotlinx.coroutines.flow.stateIn
import kotlinx.coroutines.launch

class StudentViewModel(
    private val repository: StudentRepository
) : ViewModel() {

    val students = repository.students.stateIn(
        scope = viewModelScope,
        started = SharingStarted.WhileSubscribed(5000),
        initialValue = emptyList()
    )

    fun addStudent(name: String, email: String) {
        viewModelScope.launch {
            repository.addStudent(name, email)
        }
    }

    fun deleteStudent(student: StudentEntity) {
        viewModelScope.launch {
            repository.deleteStudent(student)
        }
    }
}

Production code should also expose appropriate loading, success, and error states. For example, a duplicate-email error should be handled and communicated rather than causing an unhandled coroutine failure.

21. Common Mistakes and Troubleshooting

22. Best Practices

• Use descriptive entity, table, and column names.
• Choose primary keys that reliably identify records.
• Use foreign keys to preserve referential integrity.
• Index foreign-key columns and other frequently queried fields where useful.
• Use a unique constraint when a value must not be duplicated.
• Use junction tables for many-to-many relationships.
• Use @Embedded for nested values that belong in the same table.
• Use @Relation and @Transaction for appropriate related-data queries.
• Prefer parameterized SQL rather than constructing query strings from user input.
• Choose conflict and delete rules according to business requirements.
• Keep database operations in DAOs and repositories instead of composable functions.
• Use Flow for reactive data and coroutines for one-time operations.
• Test foreign keys, uniqueness constraints, transactions, and migrations.
• Export and version-control database schemas for reliable migration testing.
• Avoid storing secrets in ordinary database fields without a suitable security design.

23. Practice Exercise

Project: Create a Student Course Enrollment application using Room.

Requirements:

1. Create a StudentEntity with a generated ID, name, and unique email.
2. Create a CourseEntity with a generated ID and title.
3. Create a junction entity linking students and courses.
4. Implement DAO functions to insert, update, query, and delete students and courses.
5. Retrieve a student together with all enrolled courses.
6. Retrieve a course together with all enrolled students.
7. Prevent duplicate enrollment using a composite primary key.
8. Use a transaction when multiple writes must succeed together.
9. Display the data using a ViewModel and Jetpack Compose.
10. Write tests for valid enrollment, duplicate enrollment, and deletion behavior.

24. Interview Questions and Answers

Q1. What is a Room entity?

An entity is a class annotated with @Entity that represents a table in the database.

Q2. What is the purpose of a DAO?

A DAO defines the database operations available to the application, including insertions, updates, deletions, and SQL queries.

Q3. What is a foreign key?

A foreign key connects a child-table column to a referenced key in another table and helps maintain referential integrity.

Q4. How is a one-to-one relationship enforced?

Use a foreign key from one table to another and enforce uniqueness on the child foreign-key column.

Q5. How do you implement a one-to-many relationship?

Store the parent's ID as a foreign key in each child row, then query the parent and matching children using @Relation or SQL.

Q6. How do you implement a many-to-many relationship?

Create a junction table containing foreign keys for both related entities. A composite primary key can prevent duplicate associations.

Q7. What is the difference between @Embedded and @Relation?

@Embedded flattens an object's properties into the containing table or result object. @Relation describes how related rows from separate entities are retrieved.

Q8. Why use @Transaction for relationship queries?

It allows related data to be read consistently within a transaction while Room loads the associated records.

Q9. What is a junction table?

A junction table stores associations between two entities and is commonly used to represent many-to-many relationships.

Q10. Why should database operations not run on the main thread?

Database work can take time and block rendering, making the application unresponsive. Suspending DAO methods and coroutines help keep such work off the UI thread.

25. Quick Revision

• @Entity defines a table.
• @PrimaryKey uniquely identifies a record.
• @ColumnInfo customizes a column.
• @Index supports efficient queries and can enforce uniqueness.
• @Dao defines database access methods.
• @Insert, @Update, @Delete, and @Query support CRUD operations.
• Foreign keys connect related tables.
• One-to-one relationships commonly require a unique foreign key.
• One-to-many relationships use a foreign key in the child table.
• Many-to-many relationships use a junction table.
• @Embedded flattens nested properties.
• @Relation retrieves related entities.
• @Transaction supports consistent multi-step operations.
• Repositories and ViewModels help separate persistence logic from the UI.

26. Official Learning Resources

• Android Developers: Room Database Overview
• Android Developers: Defining Room Entities
• Android Developers: Accessing Data with DAOs
• Android Developers: Room Relationships

Conclusion

Implementing entities, DAOs, and relationships correctly is essential for building reliable Android applications with Room. Entities define the database structure, DAOs provide controlled access to records, and foreign keys or junction tables represent relationships between tables. By combining these components with transactions, coroutines, Flow, repositories, and ViewModels, developers can create maintainable applications with consistent and reactive local data storage.

Explore the course: Android App Development Training | Register for a Demo | JustAcademy Homepage